package com.fxjava.converexcel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;

public class ExcelHeaderAndColumnsRemover {

    private static final String INPUT_FILE = "D:\\ANR-WORK\\售后卡顿机器分析.xlsx";
    private static final String OUTPUT_FILE = "D:\\ANR-WORK\\售后卡顿机器分析_修改后.xlsx";

    private static final Map<String, String> HEADER_RENAME_MAP = new LinkedHashMap<>();
    static {
        HEADER_RENAME_MAP.put("日期", "date_day");
        HEADER_RENAME_MAP.put("来源", "source");
        HEADER_RENAME_MAP.put("接收人", "receiver");
        HEADER_RENAME_MAP.put("型号", "model");
        HEADER_RENAME_MAP.put("SN号", "sn");
        HEADER_RENAME_MAP.put("详细描述", "detail_desc");
        HEADER_RENAME_MAP.put("机器归还日期", "expected_feedback_date");
        HEADER_RENAME_MAP.put("研发初筛日期", "rd_first_review_date");
        HEADER_RENAME_MAP.put("研发初筛", "rd_initial_screening");
        HEADER_RENAME_MAP.put("研发定位", "rd_diagnosis");
        HEADER_RENAME_MAP.put("问题分类", "issue_category");
        HEADER_RENAME_MAP.put("是否修复", "is_fixed");
        HEADER_RENAME_MAP.put("修复方案", "fix_plan");
        HEADER_RENAME_MAP.put("厂商分析结果", "vendor_analysis_result");
    }

    // 需要删除整列（包含表头与所有数据）
    private static final List<String> COLUMNS_TO_DELETE = Arrays.asList(
            "相关视频&图片",
            "附件",
            "研发定位日期",
            "送老化日期",
            "老化定位",
            "老化完成日期",
            "相关信息"          // ← 新增这一行
    );


    public static void main(String[] args) throws Exception {
        Path input = Paths.get(INPUT_FILE);
        Path output = Paths.get(OUTPUT_FILE);

        try (FileInputStream fis = new FileInputStream(input.toFile());
             Workbook wb = WorkbookFactory.create(fis)) {

            Sheet sheet = wb.getSheetAt(0);
            Row header = sheet.getRow(0);
            if (header == null) throw new IllegalStateException("首行为空！");

            // 建立表头 -> 列索引映射
            Map<String, Integer> headerIndex = buildHeaderIndex(header);
            System.out.println("=== Header dump (raw -> norm) ===");


            // 1. 重命名表头
            for (Map.Entry<String, String> e : HEADER_RENAME_MAP.entrySet()) {
                String oldName = e.getKey();
                String newName = e.getValue();
                Integer idx = headerIndex.get(norm(oldName));
                if (idx != null) {
                    Cell cell = CellUtil.getCell(header, idx);
                    cell.setCellValue(newName);
                } else {
                    System.out.println("未找到要重命名的表头：" + oldName);
                }
            }

            List<Integer> colIdxToDelete = new ArrayList<>();
            for (String name : COLUMNS_TO_DELETE) {
                List<Integer> idxs = findAllColumnIndexes(header, name);
                if (idxs.isEmpty()) {
                    System.out.println("未找到要删除的列（含重复扫描）：" + name);
                } else {
                    colIdxToDelete.addAll(idxs);
                }
            }
            colIdxToDelete.sort(Collections.reverseOrder());

            colIdxToDelete.sort(Collections.reverseOrder());
            for (Integer colIdx : colIdxToDelete) {
                deleteColumn(sheet, colIdx);
            }

            // 3. 保存
            try (FileOutputStream fos = new FileOutputStream(output.toFile())) {
                wb.write(fos);
            }
            System.out.println("处理完成！输出文件已保存至：" + output.toAbsolutePath());
        }
    }
    // 找到首行里所有与 target 匹配的列索引（支持换行/空格/符号差异）
    private static List<Integer> findAllColumnIndexes(Row header, String target) {
        List<Integer> indexes = new ArrayList<>();
        String targetNorm = norm(target);
        String targetComp = compressHeaderKey(targetNorm);

        short lastCell = header.getLastCellNum();
        for (int i = 0; i < lastCell; i++) {
            Cell cell = header.getCell(i, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
            if (cell == null) continue;
            String raw = cellToString(cell);
            if (raw == null || raw.isEmpty()) continue;

            String n = norm(raw);
            String nComp = compressHeaderKey(n);

            // 精确或宽松命中（任一包含另一）
            if (n.equals(targetNorm) || nComp.equals(targetComp) ||
                    n.contains(targetNorm) || targetNorm.contains(n) ||
                    nComp.contains(targetComp) || targetComp.contains(nComp)) {
                indexes.add(i);
            }
        }
        return indexes;
    }

    // 压缩：去空白、去常见符号，仅保留中英文与数字，便于“看起来一样”的匹配
    private static String compressHeaderKey(String s) {
        if (s == null) return "";
        String r = s.replaceAll("\\s+", "");
        r = r.replaceAll("[()\\[\\]【】,:;，。/\\\\、&＆\\-_]+", "");
        return r;
    }


    private static Map<String, Integer> buildHeaderIndex(Row header) {
        Map<String, Integer> map = new HashMap<>();
        short lastCell = header.getLastCellNum();
        for (int i = 0; i < lastCell; i++) {
            Cell cell = header.getCell(i, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
            if (cell == null) continue;
            String text = cellToString(cell);
            if (text != null && !text.isEmpty()) {
                map.put(norm(text), i);
            }
        }
        return map;
    }

    /** 使用 POI 原生列位移来删除整列，避免逐格复制导致的 32767 字符限制 */
    private static void deleteColumn(Sheet sheet, int colIdx) {
        // 先计算该行当前的最后一列索引（含内容的极右位置）
        int lastCol = 0;
        int lastRow = sheet.getLastRowNum();
        for (int r = 0; r <= lastRow; r++) {
            Row row = sheet.getRow(r);
            if (row != null) {
                lastCol = Math.max(lastCol, row.getLastCellNum()); // getLastCellNum() 返回的是“列数”，不是最大下标
            }
        }
        if (colIdx >= lastCol - 1) {
            // 删除的是最右列：直接把每行对应单元格移除即可
            for (int r = 0; r <= lastRow; r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                Cell last = row.getCell(colIdx);
                if (last != null) row.removeCell(last);
            }
            return;
        }

        // 关键：把 [colIdx+1, lastCol-1] 整段列左移 1 格，相当于删除 colIdx
        // 这一步不会对每个单元格做 setCellValue，从而绕开长度限制
        sheet.shiftColumns(colIdx + 1, lastCol - 1, -1);

        // 保险：把“新最右列”清掉残留（可选）
        for (int r = 0; r <= lastRow; r++) {
            Row row = sheet.getRow(r);
            if (row == null) continue;
            Cell c = row.getCell(lastCol - 1);
            if (c != null) row.removeCell(c);
        }
    }


    private static void copyCellValue(Cell src, Cell dest) {
        switch (src.getCellType()) {
            case STRING: dest.setCellValue(src.getStringCellValue()); break;
            case NUMERIC: dest.setCellValue(src.getNumericCellValue()); break;
            case BOOLEAN: dest.setCellValue(src.getBooleanCellValue()); break;
            case FORMULA: dest.setCellFormula(src.getCellFormula()); break;
            case BLANK: dest.setBlank(); break;
            default: dest.setBlank(); break;
        }
    }

    private static String cellToString(Cell cell) {
        switch (cell.getCellType()) {
            case STRING: return cell.getStringCellValue();
            case NUMERIC: return String.valueOf(cell.getNumericCellValue());
            case FORMULA: return cell.getCellFormula();
            case BOOLEAN: return String.valueOf(cell.getBooleanCellValue());
            default: return "";
        }
    }

    private static String norm(String s) {
        if (s == null) return "";
        // 处理常见的中文导出里出现的不可见/全角空白、零宽字符等
        String t = s
                // 去除零宽空白/不可见字符（BOM 等）
                .replace("\u200B", "") // ZERO WIDTH SPACE
                .replace("\u200C", "") // ZERO WIDTH NON-JOINER
                .replace("\u200D", "") // ZERO WIDTH JOINER
                .replace("\uFEFF", "") // ZERO WIDTH NO-BREAK SPACE (BOM)
                // 统一各种“空白”为普通空格
                .replace('\u00A0', ' ')  // NO-BREAK SPACE
                .replace('\u2007', ' ')  // FIGURE SPACE
                .replace('\u202F', ' ')  // NARROW NO-BREAK SPACE
                .replace('\u3000', ' ')  // IDEOGRAPHIC SPACE（全角空格）
                // 常见全角标点转半角，避免肉眼一致但字符不同
                .replace('（', '(')
                .replace('）', ')')
                .replace('：', ':')
                .replace('，', ',')
                .replace('＆', '&');
        // 规整多重空格并统一大小写
        t = t.replaceAll("\\s+", " ").trim().toLowerCase(Locale.ROOT);
        return t;
    }


}
